MySQL NULL Functions
In mysql, there are 2 functions to handle the NULL values and replace them with some default values. They are
- IFNULL() function
- COALESCE() function
IFNULL() Function
The MySQL IFNULL() function, return an alternative value if an expression is NULL.
Consider the following employees table
| empno | name | location | office_no | mobile_no |
|---|---|---|---|---|
| 001 | Andrew | India | 9898989898 | |
| 002 | Beslin | India | 9797979797 | |
| 003 | Joanna | USA | 5012323232 | |
| 004 | Rayan | Canada | 5125125121 |
Example query for IFNULL() function
SELECT name,location, IFNULL(office_no, mobile_no) AS contact_number FROM employees;
Output
| name | location | contact_number |
|---|---|---|
| Andrew | India | 9898989898 |
| Beslin | India | 9797979797 |
| Joanna | USA | 5012323232 |
| Rayan | Canada | 5125125121 |
COALESCE() function
The COALESCE() function is similar to IFNULL function to handle null values.
Example query for COALESCE() function
SELECT name,location, COALESCE(office_no, mobile_no) AS contact_number FROM employees;